package sv.gob.mh.dgii.dao.hibernate;

import java.io.Serializable;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.beanutils.BasicDynaBean;
import org.apache.commons.beanutils.BasicDynaClass;
import org.apache.commons.beanutils.DynaBean;
import org.apache.commons.beanutils.PropertyUtils;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Example;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Restrictions;
import org.hibernate.internal.SQLQueryImpl;
import org.hibernate.jdbc.Work;
import org.hibernate.metadata.ClassMetadata;
import org.hibernate.type.Type;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.orm.ObjectRetrievalFailureException;
import sv.gob.mh.dgii.common.BeanHelper;
import sv.gob.mh.dgii.common.Constants;
import sv.gob.mh.dgii.common.Utils;
import sv.gob.mh.dgii.hibernate.FinderArgumentTypeFactory;
import sv.gob.mh.dgii.hibernate.SimpleFinderArgumentTypeFactory;
import sv.gob.mh.dgii.jdbc.JdbcHelper;
import sv.gob.mh.dgii.jdbc.NamedParameterStatement;
import sv.gob.mh.dgii.jdbc.Oracle10gRowSetDynaClass;

public class HibernateDaoNoCrudImpl {

	@Autowired
	private SessionFactory sessionFactory;
		
	private FinderArgumentTypeFactory argumentTypeFactory = new SimpleFinderArgumentTypeFactory();
	
	private JdbcHelper jdbcHelper;

	public void syncronize() {
		sessionFactory.getCurrentSession().flush();

	}
	
	public List executeHql(String hql) {
		return getSessionFactory().getCurrentSession()
				.createQuery(hql).setMaxResults(Constants.MAX_RESULTS).list();
	}
	
	@SuppressWarnings("rawtypes")
	public List executeCriteria(DetachedCriteria dc) {
		return dc.getExecutableCriteria(sessionFactory.getCurrentSession())
				.setFirstResult(Constants.INIT_RESULTS)
				.setMaxResults(Constants.MAX_RESULTS).list();
	}
	
	
	
	public List executeNamedQuery(String queryName, Object[] queryArgs) {
		final Query namedQuery = sessionFactory.getCurrentSession().getNamedQuery(queryName.trim());

		String[] namedParameters = namedQuery.getNamedParameters();
		if (namedParameters.length == 0) {
			setPositionalParams(queryArgs, namedQuery);
		} else {
			setNamedParams(namedParameters, queryArgs, namedQuery);
		}
		if (namedQuery instanceof SQLQueryImpl) {
			return executeSqlQuery(namedQuery, queryArgs);
		}
		List lst = namedQuery.list();
		return (List) lst;
	}
	
	private void setPositionalParams(Object[] queryArgs, Query namedQuery) {
		if (queryArgs != null) {
			for (int i = 0; i < queryArgs.length; i++) {
				Object arg = queryArgs[i];
				Type argType = argumentTypeFactory.getArgumentType(arg);
				if (argType != null) {
					namedQuery.setParameter(i, arg, argType);
				} else {
					namedQuery.setParameter(i, arg);
				}
			}
		}
	}
	
	private void setNamedParams(String[] namedParameters, Object[] queryArgs,
			Query namedQuery) {
		/*
		 * el namedParameters retorna los nombres de los parametros en orden
		 * inverso, asi que haremos el match del ultimo con el primero 
		 */

		int npl = 0;
		if (namedParameters != null) {
			npl = namedParameters.length - 1;
		}

		if (queryArgs != null) {
			for (int i = 0; i < queryArgs.length; i++) {
				Object arg = queryArgs[i];
				Type argType = argumentTypeFactory.getArgumentType(arg);
				if (argType != null) {
					namedQuery.setParameter(namedParameters[npl - i], arg,
							argType);
				} else {
					if (arg instanceof Collection) {
						namedQuery.setParameterList(namedParameters[npl - i],
								(Collection) arg);
					} else {
						namedQuery.setParameter(namedParameters[npl - i], arg);
					}
				}
			}
		}
	}
	
	private List<DynaBean> executeSqlQuery(Query query, Object[] params) {
		final Query queryFinal = query;
		final Object[] paramsFinal = params;
		final Object[] dynaBeanFinal = new Object[1];
		try {			
			sessionFactory.getCurrentSession().doWork(new Work() {
				public void execute(Connection con) throws SQLException {
					try {
						NamedParameterStatement nps = new NamedParameterStatement(
								con, Constants.SELECT_ALL
										+ queryFinal.getQueryString()
										+ Constants.WHERE_MAX_ALL);
						// crear mapa con parametro + nombre
						String[] names = queryFinal.getNamedParameters();
						Map<String, Object> pmap = new HashMap<String, Object>();
						for (int i = 0; i < names.length; i++) {
							pmap.put(names[i],
									paramsFinal[(paramsFinal.length - 1) - i]);
						}
						nps.setParameters(pmap);
						dynaBeanFinal[0] = JdbcHelper.getRows(nps);
						nps = null;
					} catch (SQLException e) {
						throw new HibernateException(e);

					}
				}
			});

			return (List<DynaBean>) dynaBeanFinal[0];
		} catch (Exception e) {
			throw new HibernateException(e);

		}

	}
	
	public List executeSqlNamedQuery(String queryName,
			Map<String, Object> queryArgs) {
		final Query namedQuery = sessionFactory.getCurrentSession().getNamedQuery(queryName.trim());
		List lst = executeSqlQuery(namedQuery, queryArgs);
		return lst;
	}
	
	public List executeSqlQuery(Query query, Map<String, Object> params) {
		final Query queryFinal = query;
		final Map<String, Object> paramsFinal = params;
		final Object[] dynaBeanFinal = new Object[1];

		sessionFactory.getCurrentSession().doWork(new Work() {

			public void execute(Connection con) throws SQLException {
				try {
					NamedParameterStatement nps = new NamedParameterStatement(
							con, Constants.SELECT_ALL
									+ queryFinal.getQueryString()
									+ Constants.WHERE_MAX_ALL);
					if (paramsFinal != null) {
						nps.setParameters(paramsFinal);
					}

					dynaBeanFinal[0] =  getRows(nps);
				} catch (SQLException e) {
					throw new HibernateException(e);

				}
			}
		});

		return (List) dynaBeanFinal[0];

	}
	
	/**
	 * @see
	 * @param cadena
	 *            sql y arreglo de parametros
	 * @return nothing,
	 */
	public void executeProcedure(String sql, Object[] params) throws Exception {

		final String sqlToWork = sql;
		final Object[] paramsToWork = params;

		sessionFactory.getCurrentSession().doWork(new Work() {
			public void execute(Connection conn) {
				CallableStatement cstmt = null;

				try {
					cstmt = conn.prepareCall(sqlToWork);
					if (paramsToWork != null) {
						for (int i = 0; i < paramsToWork.length; i++) {
							Object param = paramsToWork[i];							
							cstmt.setObject(i + 1, param, resolveType(param));
						}
					}					
					cstmt.execute();					
					cstmt.close();
				} catch (SQLException e) {
					try {
						cstmt.close();
					} catch (SQLException ex) {						
						throw new HibernateException(e);
					}
					throw new HibernateException(e);

				} finally {
					cstmt = null;

				}

			}
		});

	}

	private List getRows(NamedParameterStatement nps) {
		try {
			ResultSet rs = nps.executeQuery();
			Oracle10gRowSetDynaClass rsdc = new Oracle10gRowSetDynaClass(rs);

			List temp = new ArrayList();

			BasicDynaClass bdc = new BasicDynaClass("row", BasicDynaBean.class,
					rsdc.getDynaProperties());

			for (Iterator<DynaBean> rows = rsdc.getRows().iterator(); rows
					.hasNext();) {
				DynaBean oldRow = rows.next();
				DynaBean newRow = bdc.newInstance();
				PropertyUtils.copyProperties(newRow, oldRow);
				temp.add(newRow);
			}
			nps.close();
			return temp;
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}
	
	
	/**
	 * @see Object[] parameters = new Object[]{new Integer(0),new
	 *      String("CC_TMP_OMISMOR")}; Integer i =
	 *      (Integer)executeFunction("{ ? = call p_siit_functions.getNextVal(?) }"
	 *      ,parameters);
	 * @param cadena
	 *            sql y arreglo de parametros
	 * @return nothing,
	 */
	public Object executeFunction(String sql, Object[] params)
			throws SQLException {

		final Object[] outPutFinal = new Object[1];
		final String sqlFinal = sql;
		final Object[] paramsFinal = params;

		sessionFactory.getCurrentSession().doWork(new Work() {

			public void execute(Connection conn) {

				CallableStatement cstmt = null;

				try {
					cstmt = conn.prepareCall(sqlFinal);

					if (paramsFinal != null) {
						for (int i = 0; i < paramsFinal.length; i++) {
							Object param = paramsFinal[i];
							if (i == 0) {
								cstmt.registerOutParameter(1,
										resolveType(param));
							} else {
								
								cstmt.setObject(i + 1, param,
										resolveType(param));
							}
						}
					}					
					cstmt.execute();					
					Object obj = cstmt.getObject(1);
					outPutFinal[0] = obj;
					cstmt.close();
				} catch (SQLException e) {
					try {
						cstmt.close();
					} catch (SQLException ex) {						
						throw new HibernateException(e);
					}
					throw new HibernateException(e);

				} finally {
					cstmt = null;

				}

			}
		});

		return outPutFinal[0];
	}
	
	private int resolveType(Object param) {
		int sqlType = Types.VARCHAR;
		if (param == null) {
			sqlType = Types.NULL;
		} else {
			Class paramClass = param.getClass();
			if (param instanceof String) {
				sqlType = Types.VARCHAR;
			} else if (paramClass.equals(double.class)
					|| param instanceof Double) {
				sqlType = Types.DOUBLE;
			} else if (param instanceof BigDecimal) {
				sqlType = Types.NUMERIC;
			} else if (param instanceof Calendar
					|| param instanceof java.sql.Date) {
				sqlType = Types.DATE;
			} else if (paramClass.equals(int.class) || param instanceof Integer) {
				sqlType = Types.INTEGER;
			} else if (paramClass.equals(long.class) || param instanceof Long) {
				sqlType = Types.BIGINT;
			} else if (paramClass.equals(float.class) || param instanceof Float) {
				sqlType = Types.REAL;

			}
		}
		return sqlType;
	}

	
	/**
	 * 
	 * @param lst
	 *            Object list
	 * @return the first object from the list, null if its an empty list
	 */
	public Object getFirstFromList(List lst) {
		for (Iterator iter = lst.iterator(); iter.hasNext();) {
			return iter.next();
		}
		return null;
	}
	
	
	public SessionFactory getSessionFactory() {
		return sessionFactory;
	}

	public void setSessionFactory(SessionFactory sessionFactory) {
		this.sessionFactory = sessionFactory;
	}
	
	public JdbcHelper getJdbcHelper(){
		getSessionFactory().getCurrentSession().doWork(
			    new Work() {
			        public void execute(Connection connection) throws SQLException 
			        { 
			        	jdbcHelper = new JdbcHelper(connection); 
			        }
			    }
		);
		return jdbcHelper;
	}

	
	
	
}// class
